﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Data.SqlClient;
using System.Data;
using System.Windows.Forms;
using System.Reflection;

namespace QLTin.Libs
{
    public class KhachSan
    {
        public SqlConnection conn;

        public void GetConnect(int type)
        {
            try
            {
                conn.Close();
                conn.Dispose();
            }
            catch (Exception)
            {

            }

           
            conn.Open();
        }
        public void DisConnect()
        {
            if (conn.State == ConnectionState.Open)
            {
                conn.Close();
                conn.Dispose();

            }
            conn = null;
        }
        public SqlDataReader LoadData(string sql, List<SqlParameter> prm)
        {
            GetConnect(1);
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (prm != null)
            {
                foreach (SqlParameter p in prm)
                {
                    cmd.Parameters.Add(p);
                }
            }
            return cmd.ExecuteReader();
        }

        public bool InsertData(string sql, List<SqlParameter> prm, int type)
        {
            try
            {
                GetConnect(type);
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter p in prm)
                {
                    cmd.Parameters.Add(p);
                }
                cmd.ExecuteNonQuery();
                DisConnect();
                return true;
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                return false;
            }
        }

        public bool UpdateDetail(string sql, List<SqlParameter> prm)
        {
            try
            {
                GetConnect(1);
                SqlCommand cmd = new SqlCommand(sql, conn);
                cmd.CommandType = CommandType.StoredProcedure;
                foreach (SqlParameter p in prm)
                {
                    cmd.Parameters.Add(p);
                }
                cmd.ExecuteNonQuery();
                DisConnect();
                return true;
            }
            catch (Exception ex)
            {
                ex.Message.ToString();
                return false;
            }
        }

        public DataSet LoadDataSet(string query)
        {
            try
            {
                GetConnect(1);
                DataSet dt = new DataSet();
                SqlDataAdapter dataAdapter = new SqlDataAdapter(query, conn);
                dataAdapter.Fill(dt);
                conn.Close();
                return dt;
            }
            catch (Exception)
            {
                return null;
            }
        }

        public DataSet LoadDataSet(string sql, List<SqlParameter> prm)
        {
            GetConnect(1);
            SqlDataAdapter dataAdapter = new SqlDataAdapter(sql, conn);
            SqlCommand cmd = new SqlCommand(sql, conn);
            cmd.CommandType = CommandType.StoredProcedure;
            if (prm != null)
            {
                foreach (SqlParameter p in prm)
                {
                    cmd.Parameters.Add(p);
                }
            };
            dataAdapter.SelectCommand = cmd;
            DataSet ds = new DataSet();
            dataAdapter.Fill(ds);
            conn.Close();
            return ds;
        }

        public DataTable LoadDataTable(string query, int type)
        {
            GetConnect(type);
            DataTable dt = new DataTable();
            SqlDataAdapter dataAdapter = new SqlDataAdapter(query, conn);
            dataAdapter.Fill(dt);
            conn.Close();
            return dt;
        }



        //public int insertCategory(List<SqlParameter> prm)
        //{
        //    string sqlQuery = "INSERT INTO CATEGORY(name) OUTPUT INSERTED.ID VALUES(@name)";
        //    GetConnect(1);
        //    SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        //    foreach (SqlParameter p in prm)
        //    {
        //        cmd.Parameters.Add(p);
        //    }
        //    int i = (Int32)cmd.ExecuteScalar();
        //    DisConnect();
        //    return i;
        //}
        //public int insertWebsite(List<SqlParameter> prm)
        //{
        //    string sqlQuery = "INSERT INTO WEBSITE(name,url) OUTPUT INSERTED.ID VALUES(@name,@url)";
        //    GetConnect(1);
        //    SqlCommand cmd = new SqlCommand(sqlQuery, conn);
        //    foreach (SqlParameter p in prm)
        //    {
        //        cmd.Parameters.Add(p);
        //    }
        //    int i = (Int32)cmd.ExecuteScalar();
        //    DisConnect();
        //    return i;
        //}

    }
}